import sys
import os

sys.path.append(r"/data/finance.Source/collectionData/utils")

import sqlutils

connection = sqlutils.connect(dbname='futures')
#filepath = '../static/20190226/dc_20190226'
#filepath = '../static/20190226/sn_20190226'
#filepath = '../static/20190226/sc_20190226'
#filepath = '../static/20190226/zc_20190226'
#
filepaths = ['../static/20190404/dc_20190404','../static/20190404/sn_20190404','../static/20190404/sc_20190404','../static/20190404/zc_20190404']

# 逐行读取文件插入数据库
def openfile(file_path):
    with open(file_path,'r',encoding='gbk') as file_object:
        connection = sqlutils.connect(dbname='futures')
        try:
            tablename = ''
            if 'dc'in file_path:
                tablename = 'dc_tickkz'
            elif 'sn' in file_path:
                tablename = 'sn_tickkz'
            elif 'sc' in file_path:
                tablename = 'sc_tickkz'
            elif 'zc' in file_path:
                tablename = 'zc_tickkz'
            with connection.cursor() as cursor:
                line_nu = 0
                sqlstart = "INSERT INTO `"+ tablename +"` (`MarketCode`,`ContractCode`,`Time`,`NewPrice`,`OpenInt`,`Increment`,`Turnover`,`Volume`,`OpenVol`,`CloseVol`,`Type`,`Direction`,`BP1`,`SP1`,`BV1`,`SV1`) VALUES (" 
                for(r_list) in file_object:
                    if line_nu > 0:
                        r_list = r_list.replace("\n", "")
                        line_list = r_list.split(",")
                        sql = sqlstart + "'" +  line_list[0] + "','" + line_list[1] +"','" + line_list[2] + "'," + line_list[3] + "," + line_list[4] + "," +line_list[5] + "," + line_list[6] + "," + line_list[7] + "," + line_list[8] + "," + line_list[9] + ",'" + line_list[10] +"','" + line_list[11] + "',"+ line_list[12] + "," + line_list[13] + "," + line_list[14]  + ","+ line_list[15]+")"
                        print(sql)
                        cursor.execute(sql)
                    line_nu += 1
            connection.commit()
        finally:
            connection.close()

# openfile(file_path)

# 读取某个目录所有文件
def gci(filepath):
    files = os.listdir(filepath)
    for fi in files:
        if fi.endswith('.csv'):
            print(fi)
        fi_d = os.path.join(filepath,fi)            
#        print(fi_d)
        openfile(fi_d)

def forgcipaths(filepaths):
    for path in filepaths:
        gci(path)
        
forgcipaths(filepaths)
